package com.cooper.dao.item;

import java.util.List;

import com.cooper.entity.ItemInfo;
import com.cooper.pagination.PageInfo;
import com.cooper.vo.CompositeDesignerItemInfoVo;
import com.cooper.vo.DisplayItemInfoVo;
import com.cooper.vo.ItemInfoVo;
import com.cooper.vo.ItemSearchVo;

import net.paoding.rose.jade.annotation.DAO;
import net.paoding.rose.jade.annotation.Insert;
import net.paoding.rose.jade.annotation.Paged;
import net.paoding.rose.jade.annotation.ReturnGeneratedKeys;
import net.paoding.rose.jade.annotation.SQL;
import net.paoding.rose.jade.annotation.SQLParam;
import net.paoding.rose.jade.annotation.Update;

@DAO
public interface ItemInfoDAO {
	
	static final String TABLE_NAME = "d_item_info";
	
	@ReturnGeneratedKeys
	@Insert(tableName=TABLE_NAME)
	Long saveEntity(ItemInfo itemInfo) throws Exception;

	@Update(tableName=TABLE_NAME, keyColumn="id")
	boolean updateEntity(ItemInfo itemInfo) throws Exception;
	
	@SQL("update d_item_info set deleted='y',modifier=:1.modifier,gmt_modified=:1.gmtModified where id=:1.id")
	boolean deleteEntity(ItemInfo itemInfo) throws Exception;
	
	@SQL("select * from d_item_info t where t.deleted='n' and id=:1 ")
	ItemInfo loadById(long itemId) throws Exception;
	
	@SQL("select count(*) from d_item_info t where deleted='n'"
			+ " #if(0 < :1){ and seller_id=:1} "
			+ " #if(null != :2.categoryIdPath && '' != :2.categoryIdPath){ and exists (select id from d_mall_category c where t.cid=c.id and c.full_id_path like concat(:2.categoryIdPath, '%') ) } "
			+ " #if(null !=:2.keyword && '' != :2.keyword){ and (title like concat('%',:2.keyword, '%') or cn_name like concat('%',:2.keyword, '%') )} "
			)
	int queryItemInfoCount(Long userId, ItemSearchVo searchVo) throws Exception;
	
	@Paged(paramIndex=2)
	@SQL("select t.id,t.seller_id,t.cid,t.title,t.cn_name,t.price,t.quantity stock,"
			+ "(select origin_url from d_item_image a where t.id=a.item_id and a.deleted='n' and a.sort_no=1) main_url "
			+ " from d_item_info t where deleted='n'"
			+ " #if(0 < :1){ and seller_id=:1} "
			+ " #if(null != :2.categoryIdPath && '' != :2.categoryIdPath){ and exists (select id from d_mall_category c where t.cid=c.id and c.full_id_path like concat(:2.categoryIdPath, '%') ) } "
			+ " #if(null !=:2.keyword && '' != :2.keyword){ and (title like concat('%',:2.keyword, '%') or cn_name like concat('%',:2.keyword, '%') )} "
			+ " order by :2.orderBy :2.order")
	List<ItemInfoVo> queryItemInfoList(Long userId, ItemSearchVo searchVo, PageInfo pageInfo) throws Exception;
	
	@Paged(paramIndex=2)
	@SQL("select t.id,t.seller_id,t.cid,t.title,t.cn_name,t.price,t.quantity stock,"
			+ "(select origin_url from d_item_image a where t.id=a.item_id and a.deleted='n' and a.sort_no=1) main_url "
			+ " from d_item_info t where deleted='n' and seller_id=:1 and id <> :2 "
			+ " order by gmt_modified desc ")
	List<ItemInfoVo> getRelatedItemList(Long userId, Long itemId, PageInfo pageInfo) throws Exception;
	
	@Paged(paramIndex=0)
	@SQL("select t.id,t.seller_id,t.cid,t.title,t.cn_name,t.price,t.quantity stock, b.sold_out sold,"
			+ "(select origin_url from d_item_image a where t.id=a.item_id and a.deleted='n' and a.sort_no=1) main_url,"
			+ "(select count(1) from d_dis_select_config where select_type='select_item' and outer_id=t.id) display"
			+ " from d_item_info t left join d_item_stat b on(t.id=b.item_id and t.deleted='n' and b.deleted='n')  "
			+ " order by b.sold_out desc ")
	List<DisplayItemInfoVo> queryTopSoldOutItems(PageInfo pageInfo) throws Exception;
	
	@SQL("select count(1) from d_item_info t left join d_item_stat b on(t.id=b.item_id and t.deleted='n' and b.deleted='n')  ")
	int queryTopSoldOutItemsCount() throws Exception;
	
	
	@Paged(paramIndex=0)
	@SQL("select t.id,t.seller_id,t.cid,t.title,t.cn_name,t.price,t.quantity stock, b.sold_out sold,"
			+ "(select origin_url from d_item_image a where t.id=a.item_id and a.deleted='n' and a.sort_no=1) main_url"
			+ " from d_item_info t  join d_dis_select_config b on(t.id=b.outer_id and b.select_type='select_item' and t.deleted='n' and b.deleted='n')  ")
	List<ItemInfoVo> querySelectTopSoldOutItems(PageInfo pageInfo) throws Exception;
	
	@SQL("select t.id,t.seller_id,t.cid,t.title,t.cn_name,t.price,t.quantity stock,"
			+ "(select origin_url from d_item_image a where t.id=a.item_id and a.deleted='n' and a.sort_no=1) main_url,"
			+ "(select continent_name from d_address where origin='seller_address' and outer_id=d.user_id and deleted='n' ) continent_name,"
			+ " d.store_name,d.seller_name,d.seller_type,d.seller_file_id,d.favorite,d.email,d.tel,d.live_file_ids,d.company_name,d.contactor,d.brand_certificate,d.business_licence,d.passport,d.legal_passport "
			+ " from d_item_info t left join d_designer d on (t.seller_id=d.user_id and t.deleted='n' and d.deleted='n') where t.id in(:ids)" )
	List<CompositeDesignerItemInfoVo> findCompositeDesignerItemInfosByItemIds(@SQLParam("ids") List<String> ids);
	
	@Paged(paramIndex=2)
	@SQL("select t.id,t.seller_id,t.cid,t.title,t.cn_name,t.price,t.quantity stock,"
			+ "(select origin_url from d_item_image a where t.id=a.item_id and a.deleted='n' and a.sort_no=1) main_url "
			+ " from d_item_info t where deleted='n' and cid=:1 and seller_id <> :2 ")
	List<ItemInfoVo> querySomethingLike(long cid, long sellerId, PageInfo pageInfo) throws Exception;
}
